﻿using System;
using System.Collections.Generic;
using UnityEngine;
using UFrame;
using UFrame.Manage;

public class DBController : SqliteDBAccess, ILifeCycle
{
    protected string m_connectionString = @"Data Source={0}\{1};Pooling=true;FailIfMissing=false";
    protected const string TABLE_ROUTE = "route";
    protected string m_dbName;
    protected int m_randomIndex = 0;

    public bool Connect(string sourcePath)
    {
        var sourceDir = System.IO.Path.GetDirectoryName(sourcePath);
        m_dbName = System.IO.Path.GetFileName(sourcePath);
        BackUpDataBase(sourceDir);
        var opened = OpenConnection(string.Format(m_connectionString, sourceDir, m_dbName));
        if (opened && !IsExistTable("route"))
        {
            opened &= CreateRouteTable(false);
        }
        if (opened && !IsExistTable("layer"))
        {
            opened &= CreateLayerTable();
        }
        if (opened && !IsExistTable("source"))
        {
            opened &= CreateSourceTable();
        }
        return opened;
    }

    protected void BackUpDataBase(string folder)
    {
        var dbpath = System.IO.Path.Combine(folder, m_dbName);
        var backdata = System.IO.Path.Combine(folder, m_dbName + "_back_" + System.DateTime.Now.ToString("yy-MM"));
        if (!System.IO.File.Exists(backdata) && System.IO.File.Exists(dbpath))
        {
            System.IO.File.Copy(dbpath, backdata);
        }
    }

    protected bool CreateRouteTable(bool drop)
    {
        string createSql = "CREATE TABLE \"route\"(\"guid\" text NOT NULL,\"name\" TEXT,\"description\" TEXT,\"source\" integer,\"flags\" TEXT,\"icon\" text, PRIMARY KEY(\"guid\"));";
        if (drop)
            createSql = "DROP TABLE IF EXISTS \"route\";" + createSql;
        var reader = ExecuteQuery(createSql);
        return reader != null;
    }

    protected bool CreateLayerTable()
    {
        string createSql = "DROP TABLE IF EXISTS \"layer\";CREATE TABLE \"layer\" ( \"layer_id\" integer NOT NULL,\"layer_name\" TEXT,PRIMARY KEY(\"layer_id\"));";
        var reader = ExecuteQuery(createSql);
        return reader != null;
    }

    protected bool CreateSourceTable()
    {
        string createSql = "DROP TABLE IF EXISTS \"source\";CREATE TABLE \"source\" ( \"id\" integer NOT NULL,\"folder\" TEXT,PRIMARY KEY(\"id\"));";
        var reader = ExecuteQuery(createSql);
        return reader != null;
    }

    public string[] SelectFolders()
    {
        List<string> folders = new List<string>();
        var dataReader = ReadFullTable("source");
        if (dataReader != null && dataReader.HasRows)
        {
            while (dataReader.Read())
            {
                var id = dataReader.GetInt32(0);
                var folder = dataReader.GetString(1);
                folders.Add(folder);
            }
        }
        return folders.ToArray();
    }

    public bool SetSourceFolder(int id, string folder)
    {
        return InsertInto("source", new string[] { id.ToString(), folder }, true) != null;
    }

    public Dictionary<int, string> SelectLayers()
    {
        Dictionary<int, string> layerNameDic = new Dictionary<int, string>();
        var dataReader = ReadFullTable("layer");
        if (dataReader != null && dataReader.HasRows)
        {
            while (dataReader.Read())
            {
                var id = dataReader.GetInt32(0);
                var name = dataReader.GetString(1);
                layerNameDic[id] = name;
            }
        }
        return layerNameDic;
    }

    public bool UpdateLayerName(int layerId, string layerName)
    {
        return InsertInto("layer", new string[] { layerId.ToString(), layerName }, true) != null;
    }

    public List<Route> SelectAllRoute()
    {
        List<Route> datas = new List<Route>();
        var dataReader = ReadFullTable("route");
        if (dataReader != null && dataReader.HasRows)
        {
            while (dataReader.Read())
            {
                var route = new Route();
                route.guid = dataReader.GetString(0);
                route.name = dataReader.GetString(1);
                route.description = dataReader.GetString(2);
                route.source = dataReader.GetInt32(3);
                route.flag = dataReader.GetString(4);
                route.textureBase64 = dataReader.GetString(5);
                if (!string.IsNullOrEmpty(route.textureBase64))
                {
                    route.icon = StringToTexture(route.textureBase64);
                }
                datas.Add(route);
            }
        }

        return datas;
    }

    public bool DeleteRoute(string guid)
    {
        return Delete("route", new string[] { "guid" }, new string[] { guid }) != null;
    }

    public void ChangeRouteIcons(Route route, Texture2D texture)
    {
        if (texture != null)
        {
            route.textureBase64 = TextureToString(texture);
            UpdateInto(TABLE_ROUTE, new string[] { "icon" }, new string[] { route.textureBase64 }, "guid", route.guid);
        }
        else
        {
            Debug.LogError("texture empty!");
        }
    }

    public static Texture2D StringToTexture(string iconString)
    {
        try
        {
            var bytes = Convert.FromBase64String(iconString);
            Texture2D texture2d = new Texture2D(0, 0);
            if (texture2d.LoadImage(bytes))
            {
                return texture2d;
            }
        }
        catch (Exception e)
        {
            Debug.LogException(e);
        }
        return null;

    }

    public static string TextureToString(Texture2D texture)
    {
        if (texture == null)
            return "";

        var bytes = texture.EncodeToPNG();
        return Convert.ToBase64String(bytes);
    }

    public string GetNextGUID()
    {
        return TimeTool.ConvertDateTimeToLongS(System.DateTime.Now).ToString() + m_randomIndex++.ToString();
    }

    public bool InsertRoute(Route route)
    {
        if (string.IsNullOrEmpty(route.guid))
            route.guid = TimeTool.ConvertDateTimeToLongS(System.DateTime.Now).ToString() + m_randomIndex++.ToString();

        route.textureBase64 = TextureToString(route.icon);
        var reader = InsertInto("route", new string[] { route.guid,route.name,route.description,
            route.source.ToString(),route.flag, route.textureBase64 });
        if (reader != null)
        {
            reader.Close();
            return true;
        }
        return false;
    }

    public bool ChangeRouteNameAndSource(string guid, string name,string source)
    {
        return UpdateInto(TABLE_ROUTE, new string[] { "name","source" }, new string[] { name,source }, "guid", guid) != null;
    }


    public bool ChangeRouteName(string guid, string name)
    {
        return UpdateInto(TABLE_ROUTE, new string[] { "name" }, new string[] { name }, "guid", guid) != null;
    }

    public bool ChangeRouteDesc(string guid, string desc)
    {
        return UpdateInto(TABLE_ROUTE, new string[] { "description" }, new string[] { desc }, "guid", guid) != null;
    }
    public bool ChangeFlags(string guid, string flags)
    {
        return UpdateInto(TABLE_ROUTE, new string[] { "flags" }, new string[] { flags }, "guid", guid) != null;
    }

    public bool ChangeSource(string guid, int source)
    {
        return UpdateInto(TABLE_ROUTE, new string[] { "source" }, new string[] { source.ToString() }, "guid", guid) != null;
    }


    public void SelectRouteInfo(string guid)
    {

    }

    public void OnInitialize()
    {

    }

    public void OnRecover()
    {
        CloseConnection();
    }

}